/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package fr.p10.miage.gestionabsence.model;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

/**
 *
 * @author delati
 */
public class AgendaBDD {

    public AgendaBDD() {
    }
    
    public static Agenda trouverParId(int id) throws SQLException, Exception {
        Agenda agenda = new Agenda();
        String sql = "SELECT * FROM AGENDA WHERE IDCR = ? LIMIT 1";
        PreparedStatement pst = Connection.getBDD().prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        pst.setInt(1, id);
        if (pst.execute()) {
            ResultSet resultat = pst.getResultSet();
            if (resultat.first() == true) {
                agenda.init(resultat);
            } else {
                throw new Exception("Agenda inconnu");
            }
            resultat.close();
        }
        return agenda;
    }
    
    /*public static ArrayList<AgendaAffichage> getlisteAgendaPersonalise(Etudiant etudiant, String jour) throws SQLException, Exception {
        ArrayList<AgendaAffichage> listeAgenda = new ArrayList<AgendaAffichage>();
        String sql = "select distinct IDCR,salle.nom as salle,ec.nom as ec,agenda.debut as debut,agenda.fin as fin"
                + " from etudiant, promotion,agenda,jour,salle,ec,presence"
                + " where agenda.idj=jour.idj "
                + " and agenda.ids=salle.ids "
                + " and agenda.idp=promotion.idp"
                + " and agenda.idec=ec.idec "
                + " and promotion.idp=etudiant.promotion_id"
                + " and promotion.idp= ?"
                + " and jour.datee=?";
        PreparedStatement pst = Connection.getBDD().prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        pst.setInt(1, etudiant.getPromotionId());
        pst.setString(2, jour);
        
        ResultSet resultat = pst.executeQuery();
        while(resultat.next())
        {
            AgendaAffichage agenda= new AgendaAffichage();
            agenda.init(resultat);
            listeAgenda.add(agenda);
        }
        resultat.close();
        return listeAgenda;
    }   */
    

    
        public static AgendaDay getlisteAgendaPersonalise(Etudiant etudiant, String jour) throws SQLException, Exception {
        ArrayList<AgendaAffichage> listeAgenda = new ArrayList<AgendaAffichage>();
        String sql = "select distinct IDCR,salle.nom as salle,ec.nom as ec,debut,fin "
                + " from promotion,agenda,jour,salle,ec "
                + " where agenda.idj=jour.idj"
                + " and agenda.ids=salle.ids"
                + " and agenda.idp=promotion.idp"
                + " and agenda.idec=ec.idec"
                + " and promotion.idp=?"
                + " and jour.datee=? ";

        PreparedStatement pst = Connection.getBDD().prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        pst.setInt(1, etudiant.getPromotionId());
        pst.setString(2, jour);
        
        ResultSet resultat = pst.executeQuery();
        while(resultat.next())
        {
            AgendaAffichage agenda= new AgendaAffichage();
            agenda.init(resultat);
            listeAgenda.add(agenda);
        }
        resultat.close();
        return new AgendaDay(listeAgenda);
    }   
    
   /*
    public static Agenda getAgenda(Etudiant e) throws SQLException, Exception {
      
      Agenda a = new Agenda();
      SimpleDateFormat formater = null;
      Date today = new Date();
      formater = new SimpleDateFormat("dd/MM/yy");
      String sql = "select salle.nom,ec.nom,agenda.debut,agenda.fin "
              + "from etudiant, promotion,agenda,jour,salle,ec,presence "
              + "where agenda.idj=jour.idj "
              + "and agenda.ids=salle.ids "
              + "and agenda.idp=promotion.idp "
              + "and agenda.idec=ec.idec "
              + "and promotion.idp=etudiant.promotion_id "
              + "and etudiant.id=? "
              + "and jour.datee=?";
      PreparedStatement pst = Connection.getBDD().prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
      pst.setInt(1, e.getId());
      pst.setString(1,today.toString());
      if (pst.execute()) {
         ResultSet resultat = pst.getResultSet();
         if (resultat.first() == true) {
               a.init(e,today.toString(),resultat);
            } else {
               throw new Exception("Mot de passe incorrect");
            }
         } else {
            throw new Exception("Utilisateur inconnu");
         }
      pst.close();
      return a;
   }   */
}